﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_cm_ListPhotoalbum')
BEGIN
    PRINT 'Dropping Procedure proc_cm_ListPhotoalbum'
    DROP  Procedure  proc_cm_ListPhotoalbum
END
GO

PRINT 'Creating Procedure proc_cm_ListPhotoalbum'
GO

CREATE Procedure dbo.proc_cm_ListPhotoalbum
	@pHtmlPageId uniqueidentifier
	, @pAdminInd bit = 0
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

	select
		pa.[photoalbum_id]
		,pa.[html_page_id] 
		,pa.[title_txt]
		,pa.[desc_txt]
		,pa.[create_dttm]
		,pa.[update_dttm]
		,pa.[latest_photo_id]
		,p2.[filepath_txt]
		,pa.[archive_ind]
		,count(p.filename_txt) as photo_cnt
	from
		[dbo].[tbl_photoalbum] pa (nolock)
		inner join [dbo].[tbl_photo] p (nolock) on p.photoalbum_id = pa.photoalbum_id
		inner join [dbo].[tbl_photo] p2 (nolock) on pa.latest_photo_id = p2.photo_id
	where
		pa.[html_page_id] = @pHtmlPageId
		and (@pAdminInd = 1 or pa.[archive_ind] = 0)
	group by
		pa.[photoalbum_id]
		,pa.[html_page_id] 
		,pa.[title_txt]
		,pa.[desc_txt]
		,pa.[create_dttm]
		,pa.[update_dttm]
		,pa.[latest_photo_id]
		,p2.[filepath_txt]
		,pa.[archive_ind]
	order by pa.[archive_ind], pa.[create_dttm] desc
END
GO

GRANT EXEC ON dbo.proc_cm_ListPhotoalbum TO PUBLIC
GO

